﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using YunLib.Common;

namespace YunLib.DAL
{
    public abstract class ExecuteSql<TModel> where TModel : class
    {
        /// <summary> 
        /// 执行增、删、改方法 
        /// </summary> 
        /// <param name="sql"></param> 
        /// <param name="parms"></param> 
        /// <returns></returns> 
        public virtual int ExecuteNonQuery(string sql, object parms = null)
        {
            return DB.ExecuteNonQuery(sql, parms);
        }

        public virtual T Get<T>(string sql, object parms = null,Func<object,T> convertTo = null)
        {
            return DB.ExecuteScalar<T>(sql, parms, convertTo);
        }

        /// <summary>
         /// 判断对象是否存在
         /// </summary>
         /// <typeparam name="T"></typeparam>
         /// <param name="dbs"></param>
         /// <param name="ID"></param>
         /// <param name="transaction"></param>
         /// <returns></returns>
        public virtual bool Exists(string sqlString, object parms = null)
        {
            return DB.ExecuteScalar<int>(sqlString, parms) >0;
        }

        /// <summary> 
        /// Insert非Null属性的对象实例 
        /// </summary> 
        /// <param name="model"></param> 
        /// <returns></returns> 
        public virtual int Insert(string sqlString, TModel model)
        {
            //string sql = string.IsNullOrWhiteSpace(sqlString) ? DataMapping<TModel>.GetInsertSql(model) : sqlString;

            return DB.ExecuteScalar<int>(sqlString, model);
        }

        /// <summary> 
        /// Insert非Null属性的对象实例 
        /// </summary> 
        /// <param name="model"></param> 
        /// <returns></returns> 
        public virtual int Insert<T>(string sqlString, T model)
        {
            //string sql = string.IsNullOrWhiteSpace(sqlString) ? DataMapping<TModel>.GetInsertSql(model) : sqlString;

            return DB.ExecuteScalar<int>(sqlString, model);
        }

        /// <summary> 
        /// Insert非Null属性的对象实例 
        /// </summary> 
        /// <param name="model"></param> 
        /// <returns></returns> 
        public virtual int Insert(string sqlString, List<TModel> model)
        {
            return DB.ExecuteScalar<int>(sqlString, model);
        }
       
        /// <summary> 
        /// Select * 查询 
        /// </summary>     
        /// <returns></returns> 
        public virtual List<TModel> Query(string sqlString = "",object args=null)
        {
            string sql = string.IsNullOrWhiteSpace(sqlString) ? DataMapping<TModel>.GetQuerySql() : sqlString;
            return DB.Query<TModel>(sql, args);
        }

        /// <summary> 
        /// Select * 查询 
        /// </summary>     
        /// <returns></returns> 
        public virtual List<T> Query<T>(string sqlString, object args = null)
        {
            return DB.Query<T>(sqlString, args);
        }
        
        /// <summary> 
        /// 带查询条件的Select查询 
        /// </summary> 
        /// <param name="selector"></param> 
        /// <returns></returns> 
        public virtual List<TModel> Query(Func<TModel, bool> selector)
        {
            string sql = DataMapping<TModel>.GetQuerySql();
            return DB.Query<TModel>(sql, selector);
        }
        
        /// <summary> 
        /// 得到一个对象的实例 
        /// </summary> 
        /// <param name="selector"></param> 
        /// <returns></returns> 
        public virtual TModel FirstOrDefault(Func<TModel, bool> selector = null)
        {
            string sql = DataMapping<TModel>.GetQuerySql();
            return DB.FirstOrDefault<TModel>(sql, selector);
        }
       
        /// <summary> 
        /// 批量删除 
        /// </summary> 
        /// <param name="IdList"></param> 
        /// <returns></returns> 
        public virtual int Delete(string[] IdList)
        {
            return DB.ExecuteNonQuery(DataMapping<TModel>.GetDeleteSql(), new { IdList = IdList });
        }

        /// <summary> 
        /// 批量删除 
        /// </summary> 
        /// <param name="IdList"></param> 
        /// <returns></returns> 
        public virtual int Delete(string sql, object parms = null)
        {
            return DB.ExecuteNonQuery(sql, parms);
        }

        /// <summary> 
        /// 批量删除 
        /// </summary> 
        /// <param name="IdList"></param> 
        /// <returns></returns> 
        public virtual int DeleteAll()
        {
            return DB.ExecuteNonQuery(DataMapping<TModel>.GetDeleteAllSql());
        }
       
        /// <summary> 
        /// Update 一个非Null属性的对象 
        /// </summary> 
        /// <param name="model"></param> 
        /// <returns></returns> 
        public virtual int Update(string sqlString,object model = null)
        {
            //string sql = string.IsNullOrWhiteSpace(sqlString)? DataMapping<TModel>.GetUpdateSql(model):sqlString;

            return DB.ExecuteNonQuery(sqlString, model);
        }

        protected virtual List<T> GetPage<T>(string tableName, int pageIndex, int pageSize, string sortField, bool isAsc, string strWhere = "")
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendLine(@"WITH    DATAS");
            builder.AppendLine(@"          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY {0} {1} ) AS RowNumber ,".FormatWith(sortField, isAsc ? "asc" : "desc"));
            builder.AppendLine(@"                        *");
            builder.AppendLine(@"               FROM     {0}".FormatWith(tableName));
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                builder.AppendLine(" WHERE 1 =1 " + strWhere);
            }
            builder.AppendLine(@"             ),");
            builder.AppendLine(@"        T AS ( SELECT   COUNT(ID) [RowCount]");
            builder.AppendLine(@"               FROM     DATAS");
            builder.AppendLine(@"             )");
            builder.AppendLine(@"    SELECT TOP {0}".FormatWith(pageSize));
            builder.AppendLine(@"            *");
            builder.AppendLine(@"    FROM    DATAS d ,");
            builder.AppendLine(@"            T t");
            builder.AppendLine("WHERE   RowNumber > {0} * ( {1} - 1 ) order by d.ID desc ".FormatWith(pageSize, pageIndex));

            return DB.Query<T>(builder.ToString());
        }

        /// <summary>
        /// 执行一个事务队列
        /// </summary>
        /// <param name="Params"></param>
        public virtual bool ExecuteWithTransaction(List<TransactionItem> Params)
        {
            return DB.ExecuteWithTransaction(Params);
        }
        
        /// <summary> 
        /// 获取多个数据集 
        /// </summary> 
        /// <param name="sql"></param> 
        /// <param name="param"></param> 
        /// <returns></returns> 
        public virtual SqlMapper.GridReader MultyQuery(string sql, object param = null)
        {
            return DB.MultyQuery(sql, param);
        }

        /// <summary> 
        /// 获取一个数据集 
        /// </summary> 
        /// <param name="sql"></param> 
        /// <param name="param"></param> 
        /// <returns></returns> 
        public virtual DataSet MultyQueryReturnDataSet(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            return DB.MultyQueryReturnDataSet(sql, param, transaction, commandTimeout);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="storedProcedureName"></param>
        /// <returns></returns>
        public virtual List<T> ExecuteStoredProcedureReturnList<T>(string storedProcedureName, object args)
        {
            return DB.ExecuteStoredProcedureReturnList<T>(storedProcedureName, args);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="storedProcedureName"></param>
        /// <returns></returns>
        //public virtual int ExecuteStoredProcedureReturnValue(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        //{
        //    return DB.ExecuteStoredProcedure(storedProcName, parameters, out rowsAffected);
        //}

        /// <summary>
        /// 执行存储过程返回结果集
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public virtual DataSet ExecuteStoredProcedure(string storedProcedureName, object args)
        {
            return DB.ExecuteStoredProcedure(storedProcedureName, args);
        }

        /// <summary>
        /// 执行存储过程返回结果集
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public virtual string  ExecuteStoredProcedureReturnString(string storedProcedureName, object args)
        {
            DataSet ds = DB.ExecuteStoredProcedure(storedProcedureName, args);

            return ds.Tables[0].Rows[0][0].ToString();
        }

        public virtual T ExecuteStoredProcedureReturnSingle<T>(string storedProcedureName, DynamicParameters args, string outputParamName)
        {
            return DB.ExecuteStoredProcedureReturnSingle<T>(storedProcedureName, args, outputParamName);
        }

        public IDataReader GetRS(String Sql)
        {
            return DB.GetRS(Sql);
        }
    }
}
